// models/blogModel.js
const pool = require('../config/db');

const getBlogById = async (id) => {
  const [rows] = await pool.query('SELECT * FROM blogs WHERE blog_id = ?', [id]);
  return rows[0];
};
const getBlogByUserid = async (user_id) => {
  const [rows] = await pool.query('SELECT * FROM blogs WHERE user_id = ?', [user_id]);
  return rows;
};

// const getAllBlogs = async () => {
//   const [rows] = await pool.query('SELECT * FROM blogs');
//   return rows;
// };
//按关键字搜索博客
// const getAllBlogs = async (keyword = '') => {
//   let query = 'SELECT * FROM blogs';
//   let queryParams = [];

//   if (keyword) {
//     query += ' WHERE title LIKE ? OR content LIKE ?';
//     const searchKeyword = `%${keyword}%`;
//     queryParams = [searchKeyword, searchKeyword];
//   }

//   const [rows] = await pool.query(query, queryParams);
//   return rows;
// };
const getAllBlogs = async (keyword = '') => {
  let query = 'SELECT * FROM blogs';
  let queryParams = [];
  let conditions = [];

  // 检查关键字是否是有效的日期格式 (YYYY-MM-DD)
  const isDate = /^\d{4}-\d{2}-\d{2}$/.test(keyword);

  if (keyword) {
    if (isDate) {
      // 如果关键字是日期格式，将其作为发布日期进行查询
      conditions.push('DATE(created_at) = ?');
      queryParams.push(keyword);
    } else {
      // 否则，作为标题或内容的模糊搜索关键字
      conditions.push('(title LIKE ? OR content LIKE ?)');
      const searchKeyword = `%${keyword}%`;
      queryParams.push(searchKeyword, searchKeyword);
    }
  }

  if (conditions.length > 0) {
    query += ' WHERE ' + conditions.join(' AND ');
  }

  const [rows] = await pool.query(query, queryParams);
  return rows;
};

const createBlog = async (user_id,  title, content) => {
  const [result] = await pool.query('INSERT INTO blogs (user_id,  title, content) VALUES (?, ?, ?)', [user_id,  title, content]);
  return result.insertId;
};

const updateBlog = async (id, title, content) => {
  await pool.query('UPDATE blogs SET title = ?, content = ? WHERE blog_id = ?', [title, content, id]);
};

const deleteBlog = async (id) => {
  await pool.query('DELETE FROM blogs WHERE blog_id = ?', [id]);
};
const getBlogsByPage = async (page, pageSize) => {
  const limit = parseInt(pageSize);
  const offset = (page - 1) * limit;
  const [rows] = await pool.query('SELECT * FROM blogs LIMIT ? OFFSET ?', [limit, offset]);
  const [countResult] = await pool.query('SELECT COUNT(*) AS count FROM blogs');
  const totalCount = countResult[0].count;
  return { blogs: rows, totalCount };
};

// 新增方法以添加附件信息
const addAttachment = async (blog_id, file_name, file_path) => {
  await pool.query('INSERT INTO attachments (blog_id, file_name, file_path) VALUES (?, ?, ?)', [blog_id, file_name, file_path]);
};
const getAttachmentsByBlogId = async (blog_id) => {
  const [rows] = await pool.query('SELECT * FROM attachments WHERE blog_id = ?', [blog_id]);
  return rows;
};
module.exports = {
  getBlogById,
  getAllBlogs,
  createBlog,
  getBlogsByPage,
  updateBlog,
  deleteBlog,
  addAttachment,
  getBlogByUserid,
  getAttachmentsByBlogId
};
